package com.gitee.deeround.jdbchelper.core.impl;

import com.gitee.deeround.jdbchelper.DbActionConfig;
import com.gitee.deeround.jdbchelper.core.DbActionExecute;
import com.gitee.deeround.jdbchelper.exception.DbException;
import com.gitee.deeround.jdbchelper.in.*;
import com.gitee.deeround.jdbchelper.resultSetExtractors.DataTableResultSetExtractor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Duration;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class DbActionExecuteImpl extends BaseDbAction implements DbActionExecute {

    private final JdbcTemplate jdbcTemplate;

    public DbActionExecuteImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    //================execute(可执行存储过程)===================

    @Override
    public int execute(String sql) {
        CommandType commandType = isProcedure(sql) ? CommandType.StoredProcedure : CommandType.Text;
        switch (commandType) {
            case Text: {
                return executeUpdate(sql, null);
            }
            case StoredProcedure: {
                return executeProcedure(sql, null);
            }
            default: {
                throw new DbException("暂不支持的CommandType：" + commandType.toString());
            }
        }
    }

    @Override
    public int execute(String sql, List<SqlParameter> args) {
        CommandType commandType = isProcedure(sql) ? CommandType.StoredProcedure : CommandType.Text;
        switch (commandType) {
            case Text: {
                return executeUpdate(sql, args);
            }
            case StoredProcedure: {
                return executeProcedure(sql, args);
            }
            default: {
                throw new DbException("暂不支持的CommandType：" + commandType.toString());
            }
        }
    }

    @Transactional
    @Override
    public int[] batchExecute(List<String> sqls) {
        int[] result = new int[sqls.size()];
        for (int i = 0; i < sqls.size(); i++) {
            result[i] = execute(sqls.get(i));
        }
        return result;
    }

    private int executeUpdate(String sql, List<SqlParameter> args) {
        SqlStatement statement = resolve(sql, args, CommandType.Text);
        if (statement.getPrepareValues() != null) {
            return jdbcTemplate.update(sql, statement.getPrepareValues());
        } else {
            return jdbcTemplate.update(sql);
        }
    }

    private int executeProcedure(String sql, List<SqlParameter> args) {
        SqlStatement statement = resolve(sql, args, CommandType.StoredProcedure);

        LocalDateTime start = LocalDateTime.now();
        //打印存储过程执行语句
        log.info("执行存储过程：{}，执行开始：{}", sql, start.toLocalTime());

        if (DbActionConfig.debug) {
            String finalSql = statement.getPrepareSql();
            if (args != null && args.size() > 0) {
                int i = 0;
                StringBuilder sb = new StringBuilder();
                for (char c : finalSql.toCharArray()) {
                    if (c == '?') {
                        SqlParameter p = args.get(i);
                        sb.append(p.getValue() == null ? "null" : "'" + p.getValue().toString().replace("'", "''") + "'");
                        i++;
                    } else {
                        sb.append(c);
                    }
                }
                finalSql = sb.toString();
            }
            log.info("执行存储过程：{}，完整SQL：{}", sql, finalSql);
        }

        List<SqlParameter> parameters = statement.getPrepareParameters();
        jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                CallableStatement cs = con.prepareCall(statement.getPrepareSql());
                if (parameters != null) {
                    for (int i = 0; i < parameters.size(); i++) {
                        SqlParameter parameter = parameters.get(i);
                        if (parameter.getParameterDirection().equals(ParameterDirection.Output)) {
                            cs.registerOutParameter((i + 1), parameter.getDbType());// 注册输出参数的类型
                        } else {
                            cs.setObject((i + 1), parameter.getValue());// 设置输入参数的值
                        }
                    }
                }
                return cs;
            }
        }, new CallableStatementCallback<Object>() {
            @Override
            public List<SqlParameter> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                cs.execute();
                if (parameters != null) {
                    for (int i = 0; i < parameters.size(); i++) {
                        SqlParameter parameter = parameters.get(i);
                        if (parameter.getParameterDirection().equals(ParameterDirection.Output)) {
                            Object value = cs.getObject((i + 1));
                            if (value != null && parameter.getParameterType().equals(ParameterType.Cursor)) {
                                DataTableResultSetExtractor dataTableResultSetExtractor = new DataTableResultSetExtractor(parameter.getParameterName());
                                parameter.setValue(dataTableResultSetExtractor.extractData((ResultSet) value));
                            } else {
                                parameter.setValue(value);
                            }
                        }
                    }
                }
                return null;
            }
        });


        LocalDateTime end = LocalDateTime.now();
        log.info("执行存储过程：{}，执行结束：{}", sql, end.toLocalTime());
        log.info("执行存储过程：{}，执行耗时：{}毫秒", sql, Duration.between(start, end).toMillis());


        //重新把值赋值给入参
        if (args != null && parameters != null) {
            for (int i = 0; i < args.size(); i++) {
                args.get(i).setValue(parameters.get(i).getValue());
            }
        }

        return -1;
    }


    //================delete(删除)===================

    @Override
    public int delete(String tableName, String columnName, Object columnValue) {
        return jdbcTemplate.update("delete from " + tableName + " where " + columnName + "=?", new Object[]{columnValue});
    }

    @Override
    public int delete(String tableName, String columnName, List<Object> columnValue) {

        if (columnValue.size() == 1) {
            return jdbcTemplate.update("delete from " + tableName + " where " + columnName + "=?", new Object[]{columnValue.get(0)});
        }

        int batchSize = 500;
        int result = 0;
        String sql = "delete from " + tableName + " where " + columnName + " in (";
        StringBuilder sb = new StringBuilder();
        String sqlEnd = ")";
        List<Object> ps = new ArrayList<>();

        //删除，使用in语法进行删除，由于in参数个数有限制，采用每500个进行拆分一次
        for (int i = 0; i < columnValue.size(); i += batchSize) {

            sb.setLength(0);
            ps.clear();

            int e = Math.min(columnValue.size(), i + batchSize);
            for (int i1 = i; i1 < e; i1++) {
                sb.append(",").append("?");
                ps.add(columnValue.get(i1));
            }

            result += jdbcTemplate.update(sql + sb.toString().substring(1) + sqlEnd, ps.toArray());
        }

        return result;
    }


    //================update(新增/修改/删除)===================

    @Override
    public int update(String sql) {
        return jdbcTemplate.update(sql);
    }

    @Override
    public int update(String sql, Object[] args) {
        return jdbcTemplate.update(sql, args);
    }

    @Transactional
    @Override
    public int[] batchUpdate(List<String> sqls) {
        return jdbcTemplate.batchUpdate(sqls.toArray(new String[0]));
    }

    @Transactional
    @Override
    public int[] batchUpdate(String sql, List<Object[]> args) {
        return jdbcTemplate.batchUpdate(sql, args);
    }

    @Transactional
    @Override
    public int[] batchUpdate(List<String> sqls, int batch) {
        if (batch <= 0 || sqls.size() <= batch) {
            return jdbcTemplate.batchUpdate(sqls.toArray(new String[0]));
        }

        int[] result = new int[sqls.size()];
        int size = sqls.size();
        int page = size % batch == 0 ? size / batch : (size / batch) + 1;
        for (int i = 0; i < page; i++) {
            List<String> temp;
            if (i == page - 1) {
                temp = sqls.subList(i * batch, size);
            } else {
                temp = sqls.subList(i * batch, (i + 1) * batch);
            }
            int[] ints = jdbcTemplate.batchUpdate(temp.toArray(new String[0]));
            for (int i1 = 0; i1 < ints.length; i1++) {
                result[i1 + (i * batch)] = ints[i1];
            }
        }
        return result;
    }

    @Transactional
    @Override
    public int[] batchUpdate(String sql, List<Object[]> args, int batch) {
        if (batch <= 0 || args.size() <= batch) {
            return jdbcTemplate.batchUpdate(sql, args);
        }

        int[] result = new int[args.size()];
        int size = args.size();
        int page = size % batch == 0 ? size / batch : (size / batch) + 1;
        for (int i = 0; i < page; i++) {
            List<Object[]> temp;
            if (i == page - 1) {
                temp = args.subList(i * batch, size);
            } else {
                temp = args.subList(i * batch, (i + 1) * batch);
            }
            int[] ints = jdbcTemplate.batchUpdate(sql, temp);
            for (int i1 = 0; i1 < ints.length; i1++) {
                result[i1 + (i * batch)] = ints[i1];
            }
        }
        return result;
    }

}
